using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using Fan.Dongping.DataBase;
using Fan.Dongping.Common.DBMeta;
using Fan.Dongping.Common;

namespace Fan.Dongping.Ali.DataAccess
{
    /// <summary>
    /// upload file
    /// </summary>
    public class FileDA
    {
        /// <summary>
        /// get a file
        /// </summary>
        /// <param name="fileNo"></param>
        /// <returns></returns>
        public static DataRow GetFile(string fileNo)
        {
            string sql = "select * from fileUpload where fileNo = @fileNo";
            SqlParameter[] para = new SqlParameter[1];
            para[0] = new SqlParameter("@fileNo", fileNo);

            DataSet ds = DA_Base.RunReturnDataset(sql, para);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileNo"></param>
        /// <returns></returns>
        public static string GetFilePath(string fileNo)
        {
            string sql = "select FileName from FileUpload where FileNo = @FileNo";
            SqlParameter[] para = new SqlParameter[1];
            para[0] = new SqlParameter("@FileNo", fileNo);

            return DA_Base.ExecuteScalar(sql, para) as string;
        }

        /// <summary>
        /// Upload A File,return fileNo
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="description"></param>
        /// <param name="fileBody"></param>
        /// <param name="fileType"></param>
        /// <param name="uploadUser"></param>
        /// <returns>return fileNo</returns>
        public static int UploadAFile(string fileName, string description, byte[] fileBody, string fileType, string uploadUser)
        {
            string sql = "INSERT INTO fileUpload (fileName,fileBody,description,fileType, uploadUser, uploadTime) VALUES (@fileName,@fileBody,@description,@fileType,@uploadUser,GetDate()) ";
            sql += " select @@identity";

            SqlParameter[] paras = new SqlParameter[5];
            paras[0] = new SqlParameter("@fileName", fileName);
            paras[1] = new SqlParameter("@fileBody", fileBody);
            paras[2] = new SqlParameter("@description", description);
            paras[3] = new SqlParameter("@uploadUser", uploadUser);
            paras[4] = new SqlParameter("@fileType", fileType);

            decimal dd = (decimal)DA_Base.ExecuteScalar(sql, paras);
            return Convert.ToInt32(dd);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="fileSize"></param>
        /// <param name="description"></param>
        /// <param name="fileType"></param>
        /// <param name="uploadUser"></param>
        /// <returns></returns>
        public static int UploadAFile(string fileName, long fileSize, string description, string fileType, string uploadUser)
        {
            string sql = "INSERT INTO fileUpload (fileName,FileSize,description,fileType, uploadUser, uploadTime) VALUES (@fileName,@FileSize,@description,@fileType,@uploadUser,GetDate()) ";
            sql += " select @@identity";

            SqlParameter[] paras = new SqlParameter[5];
            paras[0] = new SqlParameter("@fileName", fileName);
            paras[1] = new SqlParameter("@FileSize", fileSize);
            paras[2] = new SqlParameter("@description", description);
            paras[3] = new SqlParameter("@uploadUser", uploadUser);
            paras[4] = new SqlParameter("@fileType", fileType);

            decimal dd = (decimal)DA_Base.ExecuteScalar(sql, paras);
            return Convert.ToInt32(dd);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileNo"></param>
        /// <param name="description"></param>
        /// <returns></returns>
        public static int UploadFileDesc(string fileNo, string description)
        {
            string sql = "Update FileUpload set Description=@Description where FileNo=@FileNo";
            SqlParameter[] paras = new SqlParameter[2];
            paras[0] = new SqlParameter("@FileNo", fileNo);
            paras[1] = new SqlParameter("@Description",description );

          return  DA_Base.ExecuteNonQuery(sql, paras);
        }

        /// <summary>
        /// Create File Bug Relation
        /// </summary>
        /// <param name="fileNo"></param>
        /// <param name="bugNo"></param>
        /// <param name="createUser"></param>
        /// <returns></returns>
        public static int CreateFileBugRelation(long fileNo, long bugNo, string createUser)
        {
            string sql = "INSERT INTO BugFile (FileNo,BugNo,CreateUser,CreateTime) VALUES (@fileNo,@bugNo,@CreateUser,GetDate()) ";
            SqlParameter[] paras = new SqlParameter[3];
            paras[0] = new SqlParameter("@fileNo", fileNo);
            paras[1] = new SqlParameter("@bugNo", bugNo);
            paras[2] = new SqlParameter("@CreateUser", createUser);

            return DA_Base.ExecuteNonQuery(sql, paras);
        }



        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileNo"></param>
        /// <returns></returns>
        public static long GetBugNo(string fileNo)
        {
            string sql = "select BugNo from BugFile where FileNo=@FileNo";
            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@FileNo", fileNo);
            return (long)DA_Base.ExecuteScalar(sql, paras);
        }



        /// <summary>
        /// Get bug Files
        /// </summary>
        /// <param name="bugNo"></param>
        /// <returns></returns>
        public static DataTable GetFiles(long bugNo)
        {
            string sql = "select b.*,f.* from BugFile b left join fileUpload f on b.fileNo=f.fileNo where b.bugNo =@bugNo";
            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@bugNo", bugNo);
            return DA_Base.RunReturnDataset(sql, paras).Tables[0];

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="companyName"></param>
        /// <returns></returns>
        public static long GetCompanyAllFileSize(string companyName)
        {
            string sql = "select sum(f.FileSize) from FileUpload f, BugFile bf,Bugs b,Projects p where f.FileNo=bf.FileNo and bf.BugNo=b.BugNo and b.ProNo=p.ProNo and p.CompanyName=CompanyName@";
            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@CompanyName",companyName );
            return (long) DA_Base.ExecuteScalar(sql, paras);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="bugNo"></param>
        /// <param name="errMsg"></param>
        /// <returns></returns>
        public static bool DeleteBugFiles(long bugNo, out string errMsg)
        {
            DataTable dt = GetFiles(bugNo);

            foreach (DataRow dr in dt.Rows)
            {
                long fileNo = (long)dr[FileMeta.FileNoField];

                if (DeleteFile(fileNo.ToString(), out errMsg) == false)
                {
                    return false;
                }
            }
            errMsg = null;
            return true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileNo"></param>
        /// <param name="errMsg"></param>
        /// <returns></returns>
        public static bool DeleteFile(string fileNo, out string errMsg)
        {
            if (DeleteDiskFile(fileNo, out errMsg))
            {
                DeleteUpdateFileRecord(fileNo);
                DeleteBugFileRecord(fileNo);
            }
            else
            {
                return false;
            }

            return true;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileNo"></param>
        private static void DeleteUpdateFileRecord(string fileNo)
        {
            string sql = "delete FileUpload where FileNo=@FileNo";
            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@fileNo", fileNo);
            DA_Base.ExecuteNonQuery(sql, paras);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileNo"></param>
        /// <param name="userName"></param>
        public static bool IsFileCreater(string fileNo,string userName)
        {
            string sql = "select count(*) from FileUpload where FileNo=@FileNo and UploadUser=@UploadUser";
            SqlParameter[] paras = new SqlParameter[2];
            paras[0] = new SqlParameter("@fileNo", fileNo);
            paras[1] = new SqlParameter("@UploadUser",userName );

           int i=(int) DA_Base.ExecuteScalar(sql, paras);
           if (i > 0)
           {
               return true;
           }
           else
           {
               return false;
           }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileNo"></param>
        private static void DeleteBugFileRecord(string fileNo)
        {
            string sql = "delete BugFile where FileNo=@FileNo";
            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@FileNo", fileNo);
            DA_Base.ExecuteNonQuery(sql, paras);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileNo"></param>
        /// <param name="errMsg"></param>
        /// <returns></returns>
        private static bool DeleteDiskFile(string fileNo, out string errMsg)
        {
            string path = GetFilePath(fileNo);

            if (string.IsNullOrEmpty(path) == false)
            {
                string filePath = Config.Instance.DirectoryOfUpload + path;
                if (File.Exists(filePath))
                {
                    try
                    {
                        File.Delete(filePath);
                    }
                    catch (Exception e)
                    {
                        errMsg = string.Format("Delete File {0} Error! {1}", path, e.StackTrace);
                        return false;
                    }
                }
            }
            errMsg = null;
            return true;
        }
    }
}
